package com.cagtwitter.hibernate;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.exception.SQLGrammarException;

import com.cagtwitter.services.BaseParams;

public class TvShowDao {
	public void addTvShow(TvShow tvshow){
		Session session = null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			Transaction transaction = null;
			
			transaction = session.beginTransaction();
			session.save(tvshow);
			transaction.commit();
		}
		catch(SQLGrammarException sq){
			sq.getSQLException().getNextException().printStackTrace();
		}
		finally{
			session.close();
		}
	}
	
	
	public List<TvShow> getTvShows(){
		Session session=null;
		List<TvShow> tvShowList = null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			tvShowList = session.createQuery("from TvShow").list();
		}
		catch (HibernateException e) {
			e.printStackTrace();
		}
		finally{
			session.close();
		}
		return tvShowList;
	}
	
	// TVShow About Tweet Table
	
	public int getTvShowTweetCount(String name,String type){
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
	    String selectQuery="";
	    
		if(type.equals(BaseParams.ABOUT)) selectQuery = "select count(*) from AboutTvShowTweet where name='" + name + "'";
	    int count = 0;
	    Query query = session.createQuery(selectQuery);
	    
	    count = Integer.parseInt(query.list().get(0).toString());
	    
	    session.close();
	    return count;
	}
	
	public void setLastTwid(String name,String id,String type){
		Session session=null;
		List<TvShow> tvShowList = null;
		Transaction transaction = null;
		
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			transaction = session.beginTransaction();
			tvShowList = session.createQuery("from TvShow where showName='" + name + "'").list();
			
			TvShow tvShow = tvShowList.get(0);
			
			if(type.equals(BaseParams.ABOUT)) tvShow.setAboutLast_twid(id);
			
			session.update(tvShow);
			transaction.commit();
		}
		catch (HibernateException e) {
			e.printStackTrace();
		}
		finally{
			session.close();
		}
		
	}
	
	public String getLastTwid(String name,String type){
		Session session=null;
		List<TvShow> tvShowList = null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			tvShowList = session.createQuery("from TvShow where showName='" + name + "'").list();
			
			if(tvShowList.size() > 0){
				TvShow celeb = tvShowList.get(0);
				if(type.equals(BaseParams.ABOUT)) return celeb.getAboutLast_twid();
			}

		}
		catch (HibernateException e) {
			e.printStackTrace();
		}
		finally{
			session.close();
		}
		return "0";
	}
	
	public AboutTvShowTweet getLastTweetOfTvShow(String name){
		Session session=null;
		List<AboutTvShowTweet> tvShowTweetList = null;
		AboutTvShowTweet tvShowTweet=null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			tvShowTweetList = session.createQuery("select atst from TvShow tv,AboutTvShowTweet atst where atst.twid = tv.aboutLast_twid AND tv.showName = '"+name+"'").list();
			
			tvShowTweet = tvShowTweetList.get(0);
			
		}
		catch (HibernateException e) {
			e.printStackTrace();
		}
		finally{
			session.close();
		}
		return tvShowTweet;
	}
	
	public void addAboutTvShowTweet(AboutTvShowTweet aboutTvShowTweet){
		Session session = null;
		
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			Transaction transaction = null;
			
			transaction = session.beginTransaction();
			session.save(aboutTvShowTweet);
			transaction.commit();
		}
		catch(SQLGrammarException sq){
			sq.getSQLException().getNextException().printStackTrace();
		}
		finally{
			session.close();
		}
	}
	
	public List<AboutTvShowTweet> getAboutTvShowTweets(String name,int page,int count){
		Session session=null;
		List<AboutTvShowTweet> aboutTvShowTweetList = null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			Query query = session.createQuery("from AboutTvShowTweet where name='"+name+"' order by twid desc");
			query.setFirstResult((page-1) * count);
			query.setMaxResults(count);
			aboutTvShowTweetList = query.list();
			
		}
		catch (HibernateException e) {
			e.printStackTrace();
		}
		finally{
			session.close();
		}
		return aboutTvShowTweetList;
	}
	
	
}
